package com.android.projectsquirrel;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter 
{
	public static final String KEY_ROWID = "_id";
    public static final String KEY_PROFILENAME = "profileName";
    public static final String KEY_SUBMITTED_BEFORE = "submitted_before";
    public static final String KEY_LATITUDE = "latitude";
    public static final String KEY_LONGITUDE = "longitude";
    public static final String KEY_SETTING = "setting";   
    public static final String KEY_NUTBEARING = "nutbearing"; 
    public static final String KEY_SEEDBEARING = "seedbearing"; 
    public static final String KEY_FRUITBEARING = "fruitbearing"; 
    public static final String KEY_TINYSEEDED = "tinyseeded"; 
    public static final String KEY_CONIFEROUS = "coniferous"; 
    public static final String KEY_OTHERTREES = "othertrees"; 
    public static final String KEY_DOGS = "dogs"; 
    public static final String KEY_CATS = "cats";
    public static final String KEY_COYOTES = "coyotes";
    public static final String KEY_HAWKS = "hawks";
    public static final String KEY_GRAIN = "grain";
    private static final String TAG = "DBAdapter";
    
    private static final String DATABASE_NAME = "FinalDatabase";
    private static final String DATABASE_TABLE = "finalDataTable";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table "+ DATABASE_TABLE+" (profileName text primary key,"
        + "submitted_before integer not null,latitude text,longitude text,setting integer not null, nutbearing integer not null," 
        +"seedbearing integer not null,fruitbearing integer not null,tinyseeded integer not null,"
        + "coniferous integer not null,othertrees text,dogs text integer null,cats integer not null,coyotes integer not null,hawks integer not null,grain integer not null);";
        
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
            onCreate(db);
        }
    }    
    
    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
    
    //---insert a title into the database---
    public long insertTitle(String profilename,int submittedbefore,String latitude,String longitude, int setting,int nutbearing,
    		int seedbearing,int fruitbearing,int tinyseeded,int coniferous,String otherTrees,int dogs,int cats,
    		int coyotes,int hawks,int grain) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_PROFILENAME, profilename);
        initialValues.put(KEY_SUBMITTED_BEFORE, submittedbefore);
        initialValues.put(KEY_LATITUDE, latitude);
        initialValues.put(KEY_LONGITUDE, longitude);
        initialValues.put(KEY_SETTING, setting);
        initialValues.put(KEY_NUTBEARING, nutbearing);
        initialValues.put(KEY_SEEDBEARING, seedbearing);
        initialValues.put(KEY_FRUITBEARING, fruitbearing);
        initialValues.put(KEY_TINYSEEDED, tinyseeded);
        initialValues.put(KEY_CONIFEROUS, coniferous);
        initialValues.put(KEY_OTHERTREES, otherTrees);
        initialValues.put(KEY_DOGS, dogs);
        initialValues.put(KEY_CATS, cats);
        initialValues.put(KEY_COYOTES, coyotes);
        initialValues.put(KEY_HAWKS, hawks);
        initialValues.put(KEY_GRAIN, grain);
        
        long value =  db.insert(DATABASE_TABLE, null, initialValues);
//        db.close();
        return value;
    }

    //---deletes a particular title---
//    public boolean deleteTitle(long rowId) 
//    {
//        return db.delete(DATABASE_TABLE, KEY_ROWID + 
//        		"=" + rowId, null) > 0;
//    }

    //---retrieves all the titles---
    public Cursor getAllTitles() 
    {   
       Cursor c =  db.query(DATABASE_TABLE, new String[] {
//    		   	KEY_ROWID,
        		KEY_PROFILENAME, 
        		KEY_SUBMITTED_BEFORE,
        		KEY_LATITUDE,
        		KEY_LONGITUDE,
                KEY_SETTING,
                KEY_NUTBEARING, 
                KEY_SEEDBEARING,
                KEY_FRUITBEARING,
                KEY_TINYSEEDED,
                KEY_CONIFEROUS,
                KEY_OTHERTREES,
                KEY_DOGS,
                KEY_CATS,
                KEY_COYOTES,
                KEY_HAWKS,
                KEY_GRAIN},
                null, 
                null, 
                null, 
                null, 
                null);
//       db.close();
       
       return c;
    }

    //---retrieves a particular title---
    public Cursor getTitle(String profileName) throws SQLException 
    {
        Cursor mCursor =
                db.query(true,DATABASE_TABLE, new String[] {
//                		KEY_ROWID,
                		KEY_PROFILENAME, 
                		KEY_SUBMITTED_BEFORE,
                		KEY_LATITUDE,
                		KEY_LONGITUDE,
                        KEY_SETTING,
                        KEY_NUTBEARING, 
                        KEY_SEEDBEARING,
                        KEY_FRUITBEARING,
                        KEY_TINYSEEDED,
                        KEY_CONIFEROUS,
                        KEY_OTHERTREES,
                        KEY_DOGS,
                        KEY_CATS,
                        KEY_COYOTES,
                        KEY_HAWKS,
                        KEY_GRAIN
                		}, 
                		KEY_PROFILENAME+"=?",new String[]{profileName},
                		null,
                		null, 
                		null, 
                		null 
                		);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    //---updates a title---
    public boolean updateTitle(String profileName, int submittedBefore, 
         String latitude,String longitude,int setting,int nutbearing,
        int fruitbearing,int seedbearing,int tinyseeded,int coniferous,String otherTrees,int dogs,
        int cats,int coyotes,int hawks,int grain) 
     
    {
        ContentValues args = new ContentValues();
//        args.put(KEY_PROFILENAME, profileName);
        args.put(KEY_SUBMITTED_BEFORE, submittedBefore);
        args.put(KEY_LATITUDE, latitude);
        args.put(KEY_LONGITUDE, longitude);
        args.put(KEY_SETTING, setting);
        args.put(KEY_NUTBEARING, nutbearing);
        args.put(KEY_SEEDBEARING, seedbearing);
        args.put(KEY_FRUITBEARING, fruitbearing);
        args.put(KEY_TINYSEEDED, tinyseeded);
        args.put(KEY_CONIFEROUS, coniferous);
        args.put(KEY_OTHERTREES, otherTrees);
        args.put(KEY_DOGS, dogs);
        args.put(KEY_CATS, cats);
        args.put(KEY_COYOTES, coyotes);
        args.put(KEY_HAWKS, hawks);
        args.put(KEY_GRAIN, grain);
        return db.update(DATABASE_TABLE, args, 
        		KEY_PROFILENAME+"=?",new String[]{profileName})>0;
    }
}
